﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using syc.Helper;
using System.Drawing;

namespace syc.ZhiJian.Report
{
        public partial class 入库明细汇总 : System.Web.UI.Page
        {
            protected string year;
            protected string month;
            protected string RiQiStart;
            protected string RiQiEnd;
            protected string RiQi;
            protected DateTime start, end;
            private string sql = @"
SELECT '油管(J55)',dbo.原料供货单位.名称, 
       COUNT(*) AS 捆数, 
	   SUM(kc.支数) AS 支数, 
	   SUM(kc.重量) AS 重量, 
	   SUM(kc.长度) AS 长度
FROM dbo.产品库存 AS kc INNER JOIN
     dbo.产品 ON kc.产品类别ID = dbo.产品.ID INNER JOIN
     dbo.原料供货单位 ON kc.原料供货ID = dbo.原料供货单位.ID INNER JOIN
     dbo.产品入库单 AS rk ON kc.入库单ID = rk.ID
WHERE (kc.产品类别ID = 1) AND
      ((kc.入库日期) between '*起始日期*' and '*截止日期*') AND
      (kc.状态!=0)
GROUP BY dbo.原料供货单位.名称,kc.原料供货ID
UNION ALL
SELECT '套管(J55)',dbo.原料供货单位.名称, 
       COUNT(*) AS 捆数, 
	   SUM(kc.支数) AS 支数, 
	   SUM(kc.重量) AS 重量, 
	   SUM(kc.长度) AS 长度
FROM dbo.产品库存 AS kc INNER JOIN
     dbo.产品 ON kc.产品类别ID = dbo.产品.ID INNER JOIN
     dbo.原料供货单位 ON kc.原料供货ID = dbo.原料供货单位.ID INNER JOIN
     dbo.产品入库单 AS rk ON kc.入库单ID = rk.ID
WHERE (kc.产品类别ID = 2) AND
      ((kc.入库日期) between '*起始日期*' and '*截止日期*') AND
      (kc.状态!=0)
GROUP BY dbo.原料供货单位.名称,kc.原料供货ID
UNION ALL
SELECT '油管(N80)',dbo.原料供货单位.名称, 
       COUNT(*) AS 捆数, 
	   SUM(kc.支数) AS 支数, 
	   SUM(kc.重量) AS 重量, 
	   SUM(kc.长度) AS 长度
FROM dbo.产品库存 AS kc INNER JOIN
     dbo.产品 ON kc.产品类别ID = dbo.产品.ID INNER JOIN
     dbo.原料供货单位 ON kc.原料供货ID = dbo.原料供货单位.ID INNER JOIN
     dbo.产品入库单 AS rk ON kc.入库单ID = rk.ID
WHERE (kc.产品类别ID = 1) AND
      ((kc.入库日期) between '*起始日期*' and '*截止日期*') AND
      (kc.状态!=0)
GROUP BY dbo.原料供货单位.名称,kc.原料供货ID
UNION ALL
SELECT '套管(N80)',dbo.原料供货单位.名称, 
       COUNT(*) AS 捆数, 
	   SUM(kc.支数) AS 支数, 
	   SUM(kc.重量) AS 重量, 
	   SUM(kc.长度) AS 长度
FROM dbo.产品库存 AS kc INNER JOIN
     dbo.产品 ON kc.产品类别ID = dbo.产品.ID INNER JOIN
     dbo.原料供货单位 ON kc.原料供货ID = dbo.原料供货单位.ID INNER JOIN
     dbo.产品入库单 AS rk ON kc.入库单ID = rk.ID
WHERE (kc.产品类别ID = 2) AND
      ((kc.入库日期) between '*起始日期*' and '*截止日期*') AND
      (kc.状态!=0)
GROUP BY dbo.原料供货单位.名称,kc.原料供货ID
";
            protected static DateTime date;
            protected static string fenchang;

            protected void Page_Load(object sender, EventArgs e)
            {
                this.Message.Text = "·准备就绪";
                if (!this.IsPostBack)
                {
                    //调用通用处理逻辑段【各个下拉列表变化时都调用了它】
                    DList_month_SelectedIndexChanged(sender, e);
                    Date1.TextChanged += new EventHandler(Date_TextChanged);
                    Date2.TextChanged += new EventHandler(Date_TextChanged);

                }
                GridViewHelper helper = new GridViewHelper(this.GridView1);
                helper.RegisterGroup("Column1", true, true);
                helper.RegisterSummary("捆数", SummaryOperation.Sum, "Column1");
                helper.RegisterSummary("支数", SummaryOperation.Sum, "Column1");
                helper.RegisterSummary("重量", SummaryOperation.Sum, "Column1");
                helper.RegisterSummary("长度", SummaryOperation.Sum, "Column1");
                helper.GroupHeader += new GroupEvent(helper_GroupHeader);
                helper.GroupSummary += new GroupEvent(helper_GroupSummary);
                helper.ApplyGroupSort();

                Date1.TextChanged += new EventHandler(DList_month_SelectedIndexChanged);
                Date2.TextChanged += new EventHandler(DList_month_SelectedIndexChanged);


            }

            //自定义分组标题样式
            private void helper_GroupHeader(string groupName, object[] values, GridViewRow row)
            {
                row.Cells[0].HorizontalAlign = HorizontalAlign.Left;
                row.Cells[0].Height = 33;
                row.BackColor = Color.LightGray;
                row.Cells[0].Text = "&nbsp;&nbsp;" + row.Cells[0].Text;

            }
            //自定义小计标题样式
            private void helper_GroupSummary(string groupName, object[] values, GridViewRow row)
            {
                row.Cells[0].HorizontalAlign = HorizontalAlign.Center;
                row.BackColor = Color.LightSalmon;
                row.Cells[0].Text = "和计:";
                row.Height = 40;
            }

            //当两个日期框日期变化时调用此过程
            void Date_TextChanged(object sender, EventArgs e)
            {
                DList_month_SelectedIndexChanged(sender, e);
            }

            //各个下拉列表变化时都调用了它
            protected void DList_month_SelectedIndexChanged(object sender, EventArgs e)
            {

                bool success;
                success = DateTime.TryParse(Date1.Text, out start);
                switch (success)
                {
                    case true:
                        RiQiStart = start.ToString("yyyy-MM-dd");
                        break;
                    case false:
                        RiQiStart = DateTime.Now.ToString("yyyy-MM-dd");
                        Date1.Text = DateTime.Now.ToString("yyyy年MM月dd日");
                        break;
                }
                success = DateTime.TryParse(Date2.Text, out end);
                switch (success)
                {
                    case true:
                        RiQiEnd = end.ToString("yyyy-MM-dd");
                        break;
                    case false:
                        RiQiEnd = DateTime.Now.ToString("yyyy-MM-dd");
                        Date2.Text = DateTime.Now.ToString("yyyy年MM月dd日");
                        break;
                }

                sql = sql.Replace("*起始日期*", RiQiStart);
                sql = sql.Replace("*截止日期*", RiQiEnd);
                this.SqlDataSource1.SelectCommand = sql;
                this.ViewState["SelectCommand"] = sql;
                this.SqlDataSource1.SelectCommand = (string)this.ViewState["SelectCommand"];
            }

            //导出按钮
            protected void Button1_Click(object sender, EventArgs e)
            {
                //调用通用处理逻辑段【各个下拉列表变化时都调用了它】
                DList_month_SelectedIndexChanged(sender, e);
                GridView1.DataBind();
                GridViewHelper helper = new GridViewHelper(this.GridView1);
                helper.RegisterGroup("Column1", true, true);
                helper.RegisterSummary("捆数", SummaryOperation.Sum, "Column1");
                helper.RegisterSummary("支数", SummaryOperation.Sum, "Column1");
                helper.RegisterSummary("重量", SummaryOperation.Sum, "Column1");
                helper.RegisterSummary("长度", SummaryOperation.Sum, "Column1");
                helper.GroupHeader += new GroupEvent(helper_GroupHeader);
                helper.GroupSummary += new GroupEvent(helper_GroupSummary);
                helper.ApplyGroupSort();
                //var ss = GridViewExportHelper.GetExportGridContent(GridView1, false,false);
                //GridViewExportHelper.WriteResponse(ss, "application/vnd.ms-excel", true, "Filename.xls");
                Response.Clear();
                Response.AddHeader("content-disposition", "attachment;filename=FileName.xls");
                Response.Charset = "utf-8";
                // If you want the option to open the Excel file without saving than
                // comment out the line below
                Response.Cache.SetCacheability(HttpCacheability.NoCache);
                Response.ContentType = "application/vnd.xls";
                //设置EXCEL的表头信息
                var title = Date1.Text + "到" + Date2.Text + "入库汇总";
                HttpContext.Current.Response.Write(
        @"<!DOCTYPE html PUBLIC ""-//W3C//DTD XHTML 1.0 Transitional//EN"" ""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"">
<html>
<head>
<meta http-equiv=""Content-Language"" content=""zh-cn""/>
<meta http-equiv=""Content-Type"" content="""
        + HttpContext.Current.Response.ContentType + "; charset=utf-8\"/></head><body><h2><center>" + title + "</center></h2>");
                System.IO.StringWriter stringWrite = new System.IO.StringWriter();
                System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
                GridView1.RenderControl(htmlWrite);
                Response.Write(stringWrite.ToString());
                HttpContext.Current.Response.Write("</body></html>");
                Response.End();

            }

            //为了导出而重载的一个事件，具体看EXPORT TO EXCEL的文章
            public override void VerifyRenderingInServerForm(Control control)
            {

                // Confirms that an HtmlForm control is rendered for the
                //specified ASP.NET server control at run time.

            }

            //生成报表按钮
            protected void Query_Click(object sender, EventArgs e)
            {
                //调用通用处理逻辑段【各个下拉列表变化时都调用了它】
                DList_month_SelectedIndexChanged(sender, e);
            }

        }

}
